<?php

namespace Core\Database;

use PDO;

class QueryBuilder
{
    private $connection;
    private $table;
    private $conditions = [];
    private $params = [];
    private $columns = ['*'];
    private $limit;
    private $offset;
    private $orderBy = [];
    
    public function __construct(DatabaseConnection $connection)
    {
        $this->connection = $connection;
    }
    
    public function table(string $table): self
    {
        $this->table = $table;
        return $this;
    }
    
    public function select(array $columns = ['*']): self
    {
        $this->columns = $columns;
        return $this;
    }
    
    public function where(string $column, string $operator, $value): self
    {
        $this->conditions[] = "{$column} {$operator} ?";
        $this->params[] = $value;
        return $this;
    }
    
    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orderBy[] = "{$column} {$direction}";
        return $this;
    }
    
    public function limit(int $limit): self
    {
        $this->limit = $limit;
        return $this;
    }
    
    public function offset(int $offset): self
    {
        $this->offset = $offset;
        return $this;
    }
    
    public function get(): array
    {
        $sql = "SELECT " . implode(', ', $this->columns) . " FROM {$this->table}";
        
        if (!empty($this->conditions)) {
            $sql .= " WHERE " . implode(' AND ', $this->conditions);
        }
        
        if (!empty($this->orderBy)) {
            $sql .= " ORDER BY " . implode(', ', $this->orderBy);
        }
        
        if ($this->limit !== null) {
            $sql .= " LIMIT " . $this->limit;
        }
        
        if ($this->offset !== null) {
            $sql .= " OFFSET " . $this->offset;
        }
        
        $stmt = $this->connection->getPdo()->prepare($sql);
        $stmt->execute($this->params);
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function first(): ?array
    {
        $this->limit(1);
        $results = $this->get();
        return $results[0] ?? null;
    }

    public function insert(array $data): bool
    {
        $columns = array_keys($data);
        $placeholders = array_fill(0, count($columns), '?');
        
        $sql = "INSERT INTO {$this->table} (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
        
        $stmt = $this->connection->getPdo()->prepare($sql);
        return $stmt->execute(array_values($data));
    }

    public function update(array $data): bool
    {
        $setClauses = [];
        $updateParams = [];
        foreach ($data as $column => $value) {
            $setClauses[] = "{$column} = ?";
            $updateParams[] = $value;
        }
        
        $sql = "UPDATE {$this->table} SET " . implode(', ', $setClauses);
        
        if (!empty($this->conditions)) {
            $sql .= " WHERE " . implode(' AND ', $this->conditions);
            $updateParams = array_merge($updateParams, $this->params);
        }
        
        $stmt = $this->connection->getPdo()->prepare($sql);
        return $stmt->execute($updateParams);
    }

    public function delete(): bool
    {
        $sql = 'DELETE FROM ' . $this->table;

        if (!empty($this->conditions)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }
        $stmt = $this->connection->getPdo()->prepare($sql);
        return $stmt->execute($this->params);
    }
}
